﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Pharmacy
{
    class SQLaccess
    {
        private OleDbConnection Connect = null;
        //string nameBD = "BaZa_Apteka.mdb";

        /// <summary>
        /// Конструктор принемае путь к БД
        /// </summary>
        /// <param name="path"></param>
        public SQLaccess(string path) {

            Connect = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" + @"Data Source="+path);
        }
        /// <summary>
        /// Возврощает максимальное id таблици
        /// </summary>
        /// <param name="nameTable">Имя таблици</param>
        /// <returns></returns>
        public int MaxId(string nameTable) {

            try
            {
                string sql_max_id = "SELECT Max(id) FROM " + nameTable + ";";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql_max_id, Connect);
                OleDbDataReader Reader = Command.ExecuteReader();
                DataTable t = new DataTable();
                t.Load(Reader);
                Connect.Close();

                foreach (DataRow d in t.Rows)
                {
                    int tmp = 0;
                    Int32.TryParse(d[0].ToString(), out tmp);
                    return tmp;
                }
            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
                return -1;
            }
            return 0;
        }
        /// <summary>
        /// Вход пользователя в систему
        /// </summary>
        /// <param name="name">Логин</param>
        /// <param name="password">Пароль</param>
        /// <returns></returns>
        public bool Authorization(string name, string password) {

            bool result = false;
            try
            {
                string sql = "SELECT User.id FROM [User] where Name='" + name + "' and Password='" + password + "';";


                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                OleDbDataReader Reader = Command.ExecuteReader();
                DataTable t = new DataTable();
                t.Load(Reader);
                double tmp = 0;
                foreach(DataRow d in t.Rows)
                {
                    //MessageBox.Show(d[0].ToString());
                    Double.TryParse(d[0].ToString(), out tmp);
                    if (tmp > 0)
                    {
                        result = true;
                    }
                }
                Connect.Close();

            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
            }
            return result;
        }
        /// <summary>
        /// Вазврошения имени из ид
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string NameLikarstvo(int id)
        {
            string name = "";
            try
            {
                string sql = "SELECT name FROM [Lekarstvo] where id=" + id + " ;";


                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                OleDbDataReader Reader = Command.ExecuteReader();
                DataTable t = new DataTable();
                t.Load(Reader);
                foreach (DataRow d in t.Rows)
                {
                    name = d[0].ToString();
                }

                Connect.Close();


            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
            }
            return name;
        }
        /// <summary>
        /// Поиск лекарства
        /// </summary>
        /// <param name="searech">поиск</param>
        /// <param name="id">
        /// 1 - название
        /// 2 - симптомы
        /// 3 - Активное вичиство
        /// </param>
        /// <returns></returns>
        public DataTable Searech(string searech, int id)
        {
            string table = "";
            switch (id)
            {
                case 1:
                    table = "Lekarstvo";
                    break;
                case 2:
                    table = "Semptom";
                    break;
                case 3:
                    table = "Art_des";
                    break;
            }
            if (id == 1)
            {
            try
            {
                string sql = "SELECT id FROM [Lekarstvo] where name='" + searech + "' ;";


                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                OleDbDataReader Reader = Command.ExecuteReader();
                DataTable t = new DataTable();
                t.Load(Reader);
                double tmp = 0;
                foreach (DataRow d in t.Rows)
                {
                    //MessageBox.Show("Semtom = "+d[0].ToString());
                    Double.TryParse(d[0].ToString(), out tmp);
                }
                Connect.Close();

                return t;

            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
            }
            }
            try
            {
                string sql = "SELECT id FROM [" + table + "] where name='" + searech + "' ;";


                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                OleDbDataReader Reader = Command.ExecuteReader();
                DataTable t = new DataTable();
                t.Load(Reader);
                double tmp = 0;
                foreach (DataRow d in t.Rows)
                {
                    //MessageBox.Show("Semtom = "+d[0].ToString());
                    Double.TryParse(d[0].ToString(), out tmp);
                }
                if (id == 2)
                {
                    sql = "SELECT id_likarstvo FROM [poisk] where id_sastaw =" + tmp + " ;";
                }
                else
                {
                    sql = "SELECT id_l FROM [ARt_point] where id_a =" + tmp + " ;";
                }

                Command = new OleDbCommand(sql, Connect);
                Reader = Command.ExecuteReader();
                t = new DataTable();
                t.Load(Reader);
                tmp = 0;
                foreach (DataRow d in t.Rows)
                {
                    //MessageBox.Show("id likarstvo = "+d[0].ToString());
                    Double.TryParse(d[0].ToString(), out tmp);
                }

                Connect.Close();

                return t;

            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
            }
            return null;
        }
        /// <summary>
        /// Возврашает ид ликарства
        /// </summary>
        /// <param name="name">Название ликарства</param>
        /// <returns></returns>
        public int idLikarstva(string name)
        {
            try
            {
                string sql_max_id = "SELECT id FROM Lekarstvo where name='" + name + "';";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql_max_id, Connect);
                OleDbDataReader Reader = Command.ExecuteReader();
                DataTable t = new DataTable();
                t.Load(Reader);
                Connect.Close();

                foreach (DataRow d in t.Rows)
                {
                    int tmp = 0;
                    Int32.TryParse(d[0].ToString(), out tmp);
                    return tmp;
                }
            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
                return -1;
            }
            return 0;
        }
        /// <summary>
        /// Возврашает количиства ликарств
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int kol(int id)
        {
            try
            {
                string sql = "SELECT Kilichestvo FROM Nomer where id=" + id + ";";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                OleDbDataReader Reader = Command.ExecuteReader();
                DataTable t = new DataTable();
                t.Load(Reader);
                Connect.Close();

                foreach (DataRow d in t.Rows)
                {
                    int tmp = 0;
                    Int32.TryParse(d[0].ToString(), out tmp);
                    return tmp;
                }
            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
                return -1;
            }
            return 0;
        }
        /// <summary>
        /// Возврашает цену ликарств
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int prise(int id)
        {
            try
            {
                string sql = "SELECT prais FROM Nomer where id=" + id + ";";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                OleDbDataReader Reader = Command.ExecuteReader();
                DataTable t = new DataTable();
                t.Load(Reader);
                Connect.Close();

                foreach (DataRow d in t.Rows)
                {
                    int tmp = 0;
                    Int32.TryParse(d[0].ToString(), out tmp);
                    return tmp;
                }
            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
                return -1;
            }
            return 0;
        }
        /// <summary>
        /// Продажа лекарства
        /// </summary>
        /// <param name="id">Номер лекарства</param>
        /// <returns></returns>
        public int Sale(int id) {
            int kol = this.kol(id);
             kol--;
             if (kol < 0)
             { MessageBox.Show("Кончилось ликарство!"); return 0; }
             try
             {
                 string sql = "UPDATE Nomer SET Kilichestvo = "+kol+" WHERE id = "+id+";";

                 Connect.Open();
                 OleDbCommand Command = new OleDbCommand(sql, Connect);
                 Command.ExecuteNonQuery();
                 Connect.Close();

             }
             catch (Exception e)
             {
                 Connect.Close();
                 MessageBox.Show(e.Message);
             }
            return 0;
        }
        /// <summary>
        /// Форма в котор может бить лекарство
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string forma(int id)
        {
            string name = "";
            try
            {
                string sql = "SELECT id_form FROM Forma_lekarstva where id_likarstvo =" + id + ";";

                Connect.Open();
                OleDbCommand Command = new OleDbCommand(sql, Connect);
                OleDbDataReader Reader = Command.ExecuteReader();
                DataTable t = new DataTable();
                t.Load(Reader);

                foreach (DataRow d in t.Rows)
                {
                    int tmp = 0;
                    Int32.TryParse(d[0].ToString(), out tmp);
                    sql = "SELECT form FROM Form where id =" + tmp + ";";

                    Command = new OleDbCommand(sql, Connect);
                    Reader = Command.ExecuteReader();
                    DataTable dt = new DataTable();
                    dt.Load(Reader);

                    foreach (DataRow dd in dt.Rows)
                    {
                        name = dd[0].ToString();
                    }
                }
                Connect.Close();

            }
            catch (Exception e)
            {
                Connect.Close();
                MessageBox.Show(e.Message);
                return "";
            }
            return name;
        }
    }
    
}
